package com.ccyl.repository;

import com.ccyl.entity.Resource;
import com.ccyl.util.JDBCTools;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class ResourceRepository {
    //对数据表进行遍历操作。
    public List<Resource> findAll() {
        Connection connection=null;
        PreparedStatement preparedStatement=null;
        ResultSet resultSet=null;
        List<Resource> list=new ArrayList<>();
        try {
            connection=JDBCTools.getConnection();
            String sql="SELECT * FROM resources";
            preparedStatement=connection.prepareStatement(sql);
            resultSet=preparedStatement.executeQuery();
            while(resultSet.next()) {
                String seasonValue=resultSet.getString("Season");
                String episodeValue=resultSet.getString("Episode");
                String getImage=resultSet.getString("endImageURL");
                String getVideo=resultSet.getString("VideoURL");
                Resource resource=new Resource(seasonValue,episodeValue,getImage,getVideo);
                list.add(resource);
            }
        }
        catch (SQLException ex) {
            ex.printStackTrace();
        }finally {
            JDBCTools.release(connection,preparedStatement,resultSet);
        }
        return list;
    }
    //Add item into database.
    public void add(String season,String episode,String endImageURL,String videoURL) {
        Connection connection=null;
        PreparedStatement preparedStatement=null;
        List<Resource> list=new ArrayList<>();
        try {
            connection=JDBCTools.getConnection();
            String sql="INSERT INTO resources(season,episode,endImageURL,videoURL) VALUES (?,?,?,?)";
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setString(1,season);
            preparedStatement.setString(2,episode);
            preparedStatement.setString(3,endImageURL);
            preparedStatement.setString(4,videoURL);
            preparedStatement.executeUpdate();
        }
        catch (SQLException ex) {
            ex.printStackTrace();
        }finally {
            JDBCTools.release(connection,preparedStatement,null);
        }
    }
    //删除数据。
    public void deleteBySeasonAndEpisode(String season,String episode) {
        Connection connection=null;
        PreparedStatement preparedStatement=null;
        try {
            connection=JDBCTools.getConnection();
            String sql="DELETE FROM resources WHERE season=? AND episode=?";
            preparedStatement=connection.prepareStatement(sql);
            //传入数据。
            preparedStatement.setString(1,season);
            preparedStatement.setString(2,episode);
            //数据表更新。
            preparedStatement.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCTools.release(connection,preparedStatement,null);
        }
    }
    //查找对象。
    public Resource findItem(String season,String episode) {
        Connection connection=null;
        PreparedStatement preparedStatement=null;
        ResultSet resultSet=null;
        Resource resource=null;
        try {
            connection=JDBCTools.getConnection();
            String sql="SELECT * FROM resources WHERE season=? AND episode=?";
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setString(    1,season);
            preparedStatement.setString(2,episode);
            resultSet=preparedStatement.executeQuery();
            while (resultSet.next()) {
                String seasonValue=resultSet.getString("Season");
                String episodeValue=resultSet.getString("Episode");
                String getImage=resultSet.getString("endImageURL");
                String getVideo=resultSet.getString("VideoURL");
                resource=new Resource(seasonValue,episodeValue,getImage,getVideo);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCTools.release(connection,preparedStatement,resultSet);
        }
        return resource;
    }
    public void updateResource(String season,String episode,String endImageURL,String videoURL) {
        Connection connection=null;
        PreparedStatement preparedStatement=null;
        try {
            connection=JDBCTools.getConnection();
            String sql="UPDATE resources SET season=?,episode=?,endImageURL=?,videoURL=? WHERE season=? AND episode=?";
            preparedStatement=connection.prepareStatement(sql);
            //传入数据。
            preparedStatement.setString(1,season);
            preparedStatement.setString(2,episode);
            preparedStatement.setString(3,endImageURL);
            preparedStatement.setString(4,videoURL);
            preparedStatement.setString(5,season);
            preparedStatement.setString(6,episode);
            //数据表更新。
            preparedStatement.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCTools.release(connection,preparedStatement,null);
        }
    }
    //find All Season items.
    public List<String> findSeason() {
        Connection connection=null;
        PreparedStatement preparedStatement=null;
        ResultSet resultSet=null;
        List<String> list=new ArrayList<>();
        try {
            connection=JDBCTools.getConnection();
            String sql="SELECT season FROM resources";
            preparedStatement=connection.prepareStatement(sql);
            resultSet=preparedStatement.executeQuery();
            while(resultSet.next()) {
                String seasonValue=resultSet.getString("Season");
                list.add(seasonValue);
            }
        }
        catch (SQLException ex) {
            ex.printStackTrace();
        }finally {
            JDBCTools.release(connection,preparedStatement,resultSet);
        }
        return list;
    }
    //find All Episode items with season provided above.
    public List<String> findEpisode(String season) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        List<String> list = new ArrayList<>();
        try {
            connection = JDBCTools.getConnection();
            String sql = "SELECT episode FROM resources WHERE season=?";
            preparedStatement = connection.prepareStatement(sql);
            //传入数据，在指定是第几季的情况下查找对应期的截图和数据。
            preparedStatement.setString(1, season);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                String seasonValue = resultSet.getString("Episode");
                list.add(seasonValue);
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            JDBCTools.release(connection, preparedStatement, resultSet);
        }
        return list;
    }
    //test func.
    public static void main(String[] args) {
        ResourceRepository resourceRepository=new ResourceRepository();
        System.out.println(resourceRepository.findAll());
    }
}
